package mytest.list;

import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;

public class Test2 {
    public static void main(String[] args) {

        String dateStr = "2024-10-04";

        DateTimeFormatter formatter = DateTimeFormatter.ISO_LOCAL_DATE;
        DateTimeFormatter formatter2 = DateTimeFormatter.ofPattern("yyyyMM");
        LocalDate date = LocalDate.parse(dateStr, formatter);
        String previousMonth1 = date.minusMonths(1).format(formatter2);
        String previousMonth2 = date.minusMonths(2).format(formatter2);
        String previousMonth3 = date.minusMonths(3).format(formatter2);
        String previousMonth4 = date.minusMonths(4).format(formatter2);
        String previousMonth5 = date.minusMonths(5).format(formatter2);
        LocalDateTime startOfDay = date.atStartOfDay();
        LocalDateTime endOfDay = date.atTime(23, 59, 59);
        ZoneId nigeriaTimeZone = ZoneId.systemDefault();
        Instant startOfDayInstant = startOfDay.atZone(nigeriaTimeZone).toInstant();
        Instant endOfDayInstant = endOfDay.atZone(nigeriaTimeZone).toInstant();

        String startOfDayTimestampSec = String.valueOf(startOfDayInstant.getEpochSecond());
        String endOfDayTimestampSec = String.valueOf(endOfDayInstant.getEpochSecond());
        String startOfDayTimestampMs = String.valueOf(startOfDayInstant.toEpochMilli());
        String endOfDayTimestampMs = String.valueOf(endOfDayInstant.toEpochMilli());


        String sql = "insert into t_user_task_performance_report\n" +
                "select \n" +
                "NULL,\n" +
                " %s as cycle,\n" +
                " t.division_date as division_day,\n" +
                " t.user_gid as user_id,\n" +
                " t.username as user_name,\n" +
                " t.customer_group as customer_group,\n" +
                " count(distinct t.dial_task_gid) as task_count,\n" +
                " count(DISTINCT CASE WHEN t.call_length IS NOT NULL THEN t.dial_task_gid END) AS task_call_count,\n" +
                " count(DISTINCT CASE WHEN t.call_length > 0 THEN t.dial_task_gid END) AS task_connect_count,\n" +
                " count(t.call_length) as call_count,\n" +
                " count(CASE WHEN t.call_length > 0 THEN 1 END) as connect_count,\n" +
                " 0 as reach_count,\n" +
                " 1\n" +
                "from \n" +
                "(select\n" +
                "  a.dial_task_gid, \n" +
                "  a.user_gid, \n" +
                "  b.username, \n" +
                "  '%s' as division_date, \n" +
                "  t.user_classify,\n" +
                "  t.cus_cat, \n" +
                "  t.phone, \n" +
                "  c.call_length,\n" +
                "  d.customer_group\n" +
                "from (\n" +
                "  select DISTINCT dial_task_gid,user_gid \n" +
                "  from %s \n" +
                "  where create_time >= %s and create_time <= %s\n" +
                ") a \n" +
                "inner join\n" +
                "(select task_id,cus_cat,user_classify,phone from \n" +
                "%s\n" +
                "union all \n" +
                "select task_id,cus_cat,user_classify,phone from \n" +
                "%s\n" +
                "union all \n" +
                "select task_id,cus_cat,user_classify,phone from \n" +
                "%s\n" +
                "union all \n" +
                "select task_id,cus_cat,user_classify,phone from \n" +
                "%s\n" +
                "union all \n" +
                "select task_id,cus_cat,user_classify,phone from \n" +
                "%s\n" +
                "union all \n" +
                "select task_id,cus_cat,user_classify,phone from \n" +
                "%s) t\n" +
                "on a.dial_task_gid = t.task_id\n" +
                "left join t_user b on a.user_gid = b.user_id\n" +
                "left join (\n" +
                "  select task_id,user_id,call_length\n" +
                "  from %s \n" +
                "  where start_time >= %s and start_time <= %s + %s*24*60*60*1000\n" +
                ") c on a.dial_task_gid = c.task_id and a.user_gid=c.user_id\n" +
                "left join t_cus_cat_group d\n" +
                "on t.user_classify = d.user_classify and t.cus_cat = d.cus_cat\n" +
                "where d.valid_flag = 1) t\n" +
                "GROUP BY t.division_date,t.user_gid,t.username,t.customer_group;";

        String res = String.format(sql, "0", dateStr, "log_dial_task_assign_" + date.format(formatter2),
                startOfDayTimestampSec, endOfDayTimestampSec, "t_dial_task_" + date.format(formatter2),
                "t_dial_task_" + previousMonth1, "t_dial_task_" + previousMonth2, "t_dial_task_" + previousMonth3,
                "t_dial_task_" + previousMonth4, "t_dial_task_" + previousMonth5, "t_call_log_" + date.format(formatter2),
                startOfDayTimestampMs, endOfDayTimestampMs, "0");

        System.out.println(res);
    }
}
